package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import actions.ShowEventAction;

import model.EventModel;
import model.UserModel;
import model.VenueModel;


public class DBModel {
	private Context initCtx;
	private DataSource ds;
	private Connection databaseconnection;
	private Statement st;
	private PreparedStatement pst;
	private ResultSet rs;

	public DBModel() throws NamingException, SQLException{
		initCtx = new InitialContext();
		ds = (DataSource) initCtx.lookup("java:comp/env/jdbc/ClassesDbPool");
		databaseconnection = ds.getConnection();
		st=databaseconnection.createStatement();

	}
	public void commit() throws SQLException{
		databaseconnection.commit();
	}
	public void close() throws SQLException{
		databaseconnection.close();
		if(rs!=null){
			rs.close();
		}
		st.close();
		if(pst!=null){
			pst.close();
		}
	}
	public void deleteUser(int id) throws SQLException{
		if(id>0){
			PreparedStatement preparedDelete=this.databaseconnection.prepareStatement("DELETE FROM users WHERE id=?;");
			preparedDelete.setInt(1, id);
			preparedDelete.executeUpdate();
			databaseconnection.commit();
		}
	}

	public VenueModel getVenue(int id) throws SQLException {
		if(id > 0) {
			pst = databaseconnection.prepareStatement("SELECT name, address, description, capacity FROM venues WHERE id=?;");
			pst.setInt(1, id);
			rs = pst.executeQuery();

			if(rs.next()) {
				int venueId = id;
				String venueName = rs.getString(1);
				String address = rs.getString(2);
				String description = rs.getString(3);
				int capacity = rs.getInt(4);

				return new VenueModel(venueId, venueName, address, description, capacity);
			}


		}
		return new VenueModel();
	}

	public void deleteEvent(int id) throws SQLException{
		if(id>0){
			PreparedStatement preparedDelete=this.databaseconnection.prepareStatement("DELETE FROM events WHERE id=?;");
			preparedDelete.setInt(1, id);
			preparedDelete.executeUpdate();
			databaseconnection.commit();

		}
	}

	public boolean isModeratorOfEvent(String user, int eventId) throws SQLException{
		PreparedStatement check = this.databaseconnection.prepareStatement("SELECT COUNT(*) FROM users WHERE id IN " +
				"(SELECT id FROM users WHERE username=?) AND id IN (SELECT userid FROM userevents WHERE eventid=? AND userstatus=3);");
		check.setString(1,user);
		check.setInt(2,eventId);

		ResultSet rsCheck = check.executeQuery();
		rsCheck.next();
		if(rsCheck.getInt(1)!=1){
			return false;
		}
		else return true;
	}
	public boolean eventExcists(int id) throws SQLException{
		PreparedStatement count=this.databaseconnection.prepareStatement("SELECT COUNT(*) FROM events WHERE id=?;");
		count.setInt(1, id);
		ResultSet rs=count.executeQuery();
		if(rs.next()){
			if(rs.getInt(1)==1){
				return true;
			}
		}
		return false;
	}

	public void signOff(int eventId, String username) throws SQLException {
		if(eventExcists(eventId)){
			int userId=getUserId(username);
			if(userId>0){
				PreparedStatement count=this.databaseconnection.prepareStatement("SELECT COUNT(*) FROM users where id IN (SELECT id from users where username=?) AND id IN (SELECT userid FROM userevents WHERE eventid=? AND userid=?);");
				count.setString(1, username);
				count.setInt(2, eventId);
				count.setInt(3, userId);
				ResultSet rs=count.executeQuery();
				if(rs.next()){
					int isInTable=rs.getInt(1);
					if(isInTable>0){
						PreparedStatement delete=this.databaseconnection.prepareStatement("DELETE FROM userevents WHERE userid=? AND eventid=?;");
						delete.setInt(1, userId);
						delete.setInt(2, eventId);
						delete.executeUpdate();
						this.databaseconnection.commit();
					}
				}
			}

		}

	}

	public boolean isUserSignedUpForEvent(int userid,int eventid) throws SQLException{
		pst=databaseconnection.prepareStatement("SELECT COUNT(*) FROM userevents WHERE userid=? AND eventid=?;");
		pst.setInt(1, userid);
		pst.setInt(2, eventid);
		rs=pst.executeQuery();
		if(rs.next()){
			if(rs.getInt(1)>0){
				return true;
			}
		}
		return false;
	}
	public void signUpForEvent(int userid,int eventid) throws SQLException{
		pst=databaseconnection.prepareStatement("INSERT INTO userevents VALUES(?,?,?)");
		pst.setInt(1, 2);
		pst.setInt(2, userid);
		pst.setInt(3,eventid);
		pst.executeUpdate();
		databaseconnection.commit();

	}
	public EventModel getEvent(int eventId) throws SQLException{
		pst=databaseconnection.prepareStatement("SELECT * FROM events WHERE id=?;");
		pst.setInt(1, eventId);
		rs=pst.executeQuery();
		if(rs.next()){
			Timestamp starttime=rs.getTimestamp(5);
			Calendar cal=Calendar.getInstance();
			Calendar calDeadline=Calendar.getInstance();
			String startString="";
			String dateString="";
			String timeString="";
			String deadline="";
			cal.setTime(starttime);
			startString = ShowEventAction.buildTimeString(cal);
			dateString = ShowEventAction.dateString(cal);
			timeString = ShowEventAction.timeString(cal);
			calDeadline=Calendar.getInstance();
			calDeadline.setTime(starttime);
			calDeadline.add(Calendar.HOUR_OF_DAY, -1);
			deadline = ShowEventAction.buildTimeString(calDeadline);
			return setEventBeanParameters(rs, startString, deadline, dateString, timeString, cal, calDeadline);
		}
		return null;

	}
	public ArrayList<EventModel> getAllActiveEvents() throws SQLException{
		Statement st2=databaseconnection.createStatement();
		ResultSet rs2=st2.executeQuery("SELECT * FROM events WHERE starttime>Now() ORDER BY starttime ASC;");

		EventModel eventModel=null;
		Timestamp starttime=null;
		Calendar cal=null;
		Calendar calDeadline=null;
		String startString="";
		String dateString="";
		String timeString="";
		String deadline="";
		ArrayList<EventModel>events=new ArrayList<EventModel>();
		while(rs2.next()){			
			starttime = rs2.getTimestamp(5);
			cal = Calendar.getInstance();
			cal.setTime(starttime);
			startString = ShowEventAction.buildTimeString(cal);
			dateString = ShowEventAction.dateString(cal);
			timeString = ShowEventAction.timeString(cal);
			calDeadline=Calendar.getInstance();
			calDeadline.setTime(starttime);
			calDeadline.add(Calendar.HOUR_OF_DAY, -1);
			deadline = ShowEventAction.buildTimeString(cal);
			eventModel=setEventBeanParameters(rs2, startString, deadline, dateString, timeString, cal, calDeadline);
			events.add(eventModel);
		}
		return events;
	}
	
	public ArrayList<EventModel> getMyEvents(int userId) throws SQLException{
		PreparedStatement pst2=databaseconnection.prepareStatement("SELECT e.id, e.name, e.minimumcapacity, e.maximumcapacity, e.starttime, e.description, e.venueid "+
				"FROM events AS e, userevents AS ue WHERE ue.eventid=e.id AND ue.userid=? AND starttime>Now() ORDER BY starttime ASC;");
		pst2.setInt(1, userId);
		ResultSet rs2=pst2.executeQuery();

		Timestamp starttime=null;
		Calendar cal=null;
		Calendar calDeadline=null;
		String startString="";
		String dateString="";
		String timeString="";
		String deadline="";
		ArrayList<EventModel>events=new ArrayList<EventModel>();
		while(rs2.next()){			
			starttime = rs2.getTimestamp(5);
			cal = Calendar.getInstance();
			cal.setTime(starttime);
			startString = ShowEventAction.buildTimeString(cal);
			dateString = ShowEventAction.dateString(cal);
			timeString = ShowEventAction.timeString(cal);
			calDeadline=Calendar.getInstance();
			calDeadline.setTime(starttime);
			calDeadline.add(Calendar.HOUR_OF_DAY, -1);
			deadline = ShowEventAction.buildTimeString(calDeadline);
			events.add(setEventBeanParameters(rs2, startString, deadline, dateString, timeString, cal, calDeadline));
		}
		return events;
	}

	public ArrayList<VenueModel> getAllVenues() throws SQLException{
		rs=st.executeQuery("SELECT * from Venues;");
		ArrayList<VenueModel> venueList = new ArrayList<VenueModel>();
		while(rs.next()){
			venueList.add(new VenueModel(rs.getInt(1),rs.getString(2), rs.getString(3), rs.getString(4), rs.getInt(5)));
		}
		// close database connections

		return venueList;
	}

	public ArrayList<UserModel> getUsersSignedUpForEvent(int eventId) throws SQLException{
		pst= databaseconnection.prepareStatement("SELECT username,u.id FROM users AS u, events AS e, userevents AS ue WHERE e.id = ? AND e.id = ue.eventid AND u.id = ue.userid AND ue.userstatus!=1;");
		pst.setInt(1, eventId);
		ArrayList<UserModel>users=new ArrayList<UserModel>();
		UserModel user=null;
		rs=pst.executeQuery();
		while(rs.next()){
			user=new UserModel();
			user.setUsername(rs.getString(1));
			user.setId(rs.getInt(2));
			users.add(user);
		}


		return users;
	}
	public int getUserStatus(int eventId,int userId) throws SQLException{
		pst = databaseconnection.prepareStatement("SELECT userstatus FROM userevents as ue WHERE ue.userid = ? " +
				"AND ue.eventid = ?");
		pst.setInt(1, userId);
		pst.setInt(2,eventId);
		rs=pst.executeQuery();
		if(rs.next()){
			return rs.getInt(1);
		}

		return -1;
	}
	public UserModel getUserRole(String username)throws SQLException {
		pst = databaseconnection.prepareStatement("SELECT id, role FROM users AS u, userroles AS ur WHERE u.username = ? AND ur.username = u.username");
		pst.setString(1, username);
		rs=pst.executeQuery();
		if(rs.next()){
			UserModel user=new UserModel();
			user.setId(rs.getInt(1));
			user.setUserrole(rs.getString(2));
			return user;
		}


		return null;
	}
	
	public int insertEvent(String eventName, int minCapacity, int maxCapacity, Timestamp t, String desc, int venue) throws SQLException{
		//Inserting event
		pst = databaseconnection.prepareStatement("INSERT INTO events(name, minimumcapacity, maximumcapacity, starttime, description, venueid) " +
				"VALUES(?,?,?,?,?,?) RETURNING id;");
		pst.setString(1,eventName);
		pst.setInt(2,minCapacity);
		pst.setInt(3,maxCapacity);
		pst.setTimestamp(4,t);
		pst.setString(5,desc);
		pst.setInt(6,venue);
		
		ResultSet rs = pst.executeQuery();
		rs.next();
		return rs.getInt(1);
	}
	
	public void updateEvent(String eventName, int minCapacity, int maxCapacity, Timestamp t, String desc, int venue, int eventId) throws SQLException{
			pst = databaseconnection.prepareStatement("UPDATE events SET name=?, minimumcapacity=?, maximumcapacity=?, starttime=?, description=?, venueid=? " +
					"WHERE id=?;");
			pst.setString(1,eventName);
			pst.setInt(2,minCapacity);
			pst.setInt(3,maxCapacity);
			pst.setTimestamp(4,t);
			pst.setString(5,desc);
			pst.setInt(6,venue);
			pst.setInt(7,eventId);
				
			pst.executeUpdate();
	}
	
	public int getUserId(String userName) throws SQLException {
		// TODO Auto-generated method stub
		PreparedStatement id=this.databaseconnection.prepareStatement("SELECT id FROM users where username=?;");
		id.setString(1, userName);
		ResultSet rs=id.executeQuery();
		if(rs.next()){
			return rs.getInt(1);
		}
		return -1;
	}
	
	public void setAdminOfEvent(int eventId, int userId) throws SQLException{
		pst = databaseconnection.prepareStatement("INSERT INTO userevents(userstatus,userid,eventid)" +
				"VALUES(?,?,?)");
		pst.setInt(1, 3); //3: user is admin of event (1:banned, 2:participating)
		pst.setInt(2, userId);
		pst.setInt(3, eventId);
		pst.executeUpdate();
	}
	
	//This method has to be called in the action
	public boolean validVenueCapacity(int venueId, int eventCapacity) throws SQLException{
		pst = databaseconnection.prepareStatement("SELECT capacity FROM venues WHERE id=?;");
		pst.setInt(1,venueId);
		ResultSet rs = pst.executeQuery();
		rs.next();
		int capacity=rs.getInt(1);
		if(capacity>=eventCapacity) return true;
		else return false;
	}
	
	public boolean emailExists(String email, int userId) throws SQLException {
		pst = databaseconnection.prepareStatement("SELECT id FROM users WHERE email = ?;");
		pst.setString(1, email);
		rs = pst.executeQuery();
		if(rs.next()) {
			if(rs.getInt(1)!=userId){
					return true;
			}
		}
		return false;
	}
	
	public void updateUser(String email, String password, String bio, int userId) throws SQLException{
		pst=databaseconnection.prepareStatement("UPDATE users SET email=?, password=md5(?), bio=? WHERE id=?;");
		pst.setString(1,email);
		pst.setString(2,password);
		pst.setString(3,bio);
		pst.setInt(4,userId);
		pst.executeUpdate();
	}
	
	public void updateUser(String email, String bio, int userId) throws SQLException{
		pst=databaseconnection.prepareStatement("UPDATE users SET email=?, bio=? WHERE id=?;");
		pst.setString(1,email);
		pst.setString(2,bio);
		pst.setInt(3,userId);
		pst.executeUpdate();
	}
	
	public UserModel getUser(String userName) throws SQLException{
		pst=databaseconnection.prepareStatement("SELECT id, username, email, bio FROM users WHERE username=?;");
		pst.setString(1, userName);
		rs=pst.executeQuery();
		if(rs.next()){
			UserModel user = new UserModel();
			user.setId(rs.getInt(1));
			user.setUsername(rs.getString(2));
			user.setEmail(rs.getString(3));
			user.setBio(rs.getString(4));
			return user;
		}
		return null;
	}
	public UserModel getUserFromId(int id) throws SQLException{
		pst=databaseconnection.prepareStatement("SELECT id, username, email, bio FROM users WHERE id=?;");
		pst.setInt(1, id);
		rs=pst.executeQuery();
		if(rs.next()){
			UserModel user = new UserModel();
			user.setId(rs.getInt(1));
			user.setUsername(rs.getString(2));
			user.setEmail(rs.getString(3));
			user.setBio(rs.getString(4));
			return user;
		}
		return null;
	}
	
	public int insertVenue(String venueName, String venueAddress, String venueDescription, int venueCapacity) throws SQLException{
		pst = databaseconnection.prepareStatement("INSERT INTO venues(name, address, description, capacity) " +
				"VALUES(?,?,?,?) RETURNING id;");		
		pst.setString(1,venueName);
		pst.setString(2,venueAddress);
		pst.setString(3,venueDescription);
		pst.setInt(4,venueCapacity);
		rs = pst.executeQuery();
		rs.next();
		return rs.getInt(1);
	}
	
	public String getVenueName(int venueId) throws SQLException{
		pst = databaseconnection.prepareStatement("SELECT name FROM venues WHERE id=?;");	
		pst.setInt(1,venueId);
		rs = pst.executeQuery();
		rs.next();
		return rs.getString(1);
	}
	
	/**
	 * @param resultSet
	 * @param numberOfSignedUpUserAtEvent
	 * @return	number of user signed up for event with the given id
	 * @throws SQLException
	 */
	private int getNumberOfUsersSignedUpForEvent(ResultSet resultSet) throws SQLException {
		return getUsersSignedUpForEvent(resultSet.getInt(1)).size();
	}
	private EventModel setEventBeanParameters(ResultSet resultSet, String startString, String deadline) throws SQLException{
		EventModel event=new EventModel();
		event.setMinCapacity(resultSet.getInt(3));
		event.setMaxCapacity(resultSet.getInt(4));
		event.setId(resultSet.getInt(1));
		event.setVenueId(resultSet.getInt(7));
		event.setStartTime(startString);
		event.setDeadline(deadline);
		event.setEventName(resultSet.getString(2));
		event.setDescription(resultSet.getString(6));
		event.setVenueName("");
		event.setSignedUpUsers(null);
		event.setNumberOfSignedUpUsers(getNumberOfUsersSignedUpForEvent(resultSet));
		return event;
	}
	private EventModel setEventBeanParameters(ResultSet resultSet, String startString, String deadline, String date, String time, Calendar start, Calendar dead) throws SQLException{
		EventModel event=new EventModel();
		event.setMinCapacity(resultSet.getInt(3));
		event.setMaxCapacity(resultSet.getInt(4));
		event.setId(resultSet.getInt(1));
		event.setVenueId(resultSet.getInt(7));
		event.setStartTime(startString);
		event.setDeadline(deadline);
		event.setDate(date);
		event.setTime(time);
		event.setEventName(resultSet.getString(2));
		event.setDescription(resultSet.getString(6));
		event.setVenueName("");
		event.setSignedUpUsers(null);
		event.setNumberOfSignedUpUsers(getNumberOfUsersSignedUpForEvent(resultSet));
		event.setCalendarStarttime(start);
		event.setCalendarDeadline(dead);
		return event;
	}
	public ArrayList<UserModel> getAllUsers() throws SQLException {
		rs=st.executeQuery("SELECT * FROM users;");
		ArrayList<UserModel> users=new ArrayList<UserModel>();
		UserModel u;
		while(rs.next()){
			u=new UserModel();
			u.setId(rs.getInt(1));
			u.setUsername(rs.getString(2));
			u.setEmail(rs.getString(3));
			u.setBio(rs.getString(5));
			users.add(u);
		}
		return users;
	}
	public ArrayList<EventModel> getAllEvents() throws SQLException {
		rs=st.executeQuery("SELECT * FROM events;");
		ArrayList<EventModel> events=new ArrayList<EventModel>();
		EventModel event;
		while(rs.next()){
			event=new EventModel();
			event.setId(rs.getInt(1));
			event.setEventName(rs.getString(2));
			setEventTimeAndDate(rs,event);
			event.setDescription(rs.getString(6));
			events.add(event);
		}
		return events;
	}
	private void setEventTimeAndDate(ResultSet resultSetEvents, EventModel event)
			throws SQLException {
		String tempDate=resultSetEvents.getString(5).substring(0,11);//yyyy-mm-dd
		String[] tempDates = tempDate.split("-"); //yyyy mm dd
		String date = tempDates[2]+"/"+tempDates[1]+"/"+tempDates[0]; //dd/mm/yyyy
		event.setDate(date.replaceAll(" ", ""));
		event.setTime(resultSetEvents.getString(5).substring(11,16));
	}

	
	public boolean usernameExists(String username) throws SQLException {
		pst = databaseconnection.prepareStatement("SELECT id FROM users WHERE username=?;");
		pst.setString(1, username);
		rs = pst.executeQuery();
		if(rs.next()) return true;		
		return false;
	}
	
	public void insertUser(String username, String email, String password,
			String bio) throws SQLException{
		pst = databaseconnection.prepareStatement("INSERT INTO users(username, email, password, bio)" +
				"VALUES(?,?,md5(?),?)");
		pst.setString(1, username);
		pst.setString(2, email);
		pst.setString(3, password);
		pst.setString(4, bio);
		pst.executeUpdate();
		pst = databaseconnection.prepareStatement("INSERT INTO userroles(username, role) VALUES(?,?);");
		pst.setString(1, username);
		pst.setString(2, "registered");
		pst.executeUpdate();
	}
	public UserModel getEventOwner(int eventId) throws SQLException {
		pst=databaseconnection.prepareStatement("SELECT u.id, u.username FROM users as u,userevents as ue WHERE u.id=ue.userid AND ue.eventid=? AND ue.userstatus=3;");
		pst.setInt(1, eventId);
		System.out.println("eventId="+eventId);
		rs=pst.executeQuery();
		UserModel owner=null;
		if(rs.next()){
			owner=new UserModel();
			owner.setUsername(rs.getString(2));
			owner.setId(rs.getInt(1));
			System.out.println("yay, du fant en owner");
		}
		return owner;
	}
	public void banUserFromEvent(int eventId, int userIdToBan) throws SQLException {
		pst=databaseconnection.prepareStatement("UPDATE userevents SET userstatus=1 WHERE userid=? AND eventid=?;");
		pst.setInt(1, userIdToBan);
		pst.setInt(2, eventId);
		pst.executeUpdate();
		databaseconnection.commit();
	}
	public ArrayList<UserModel> getBannedUsers(int eventId) throws SQLException {
		pst= databaseconnection.prepareStatement("SELECT username,u.id FROM users AS u, events AS e, userevents AS ue WHERE e.id = ? AND e.id = ue.eventid AND u.id = ue.userid AND ue.userstatus=1;");
		pst.setInt(1, eventId);
		ArrayList<UserModel>users=new ArrayList<UserModel>();
		UserModel user=null;
		rs=pst.executeQuery();
		while(rs.next()){
			user=new UserModel();
			user.setUsername(rs.getString(1));
			user.setId(rs.getInt(2));
			users.add(user);
		}
		return users;
	}
	public void unbanUserFromEvent(int eventId, int userIdToUnBan) throws SQLException {
		pst=databaseconnection.prepareStatement("DELETE FROM userevents WHERE eventid=? AND userid=? AND userstatus=1;");
		pst.setInt(1, eventId);
		pst.setInt(2, userIdToUnBan);
		pst.executeUpdate();
		databaseconnection.commit();
	}
	
}
